IF EXISTS (SELECT * FROM sysobjects WHERE name='POSplus_TagLoc_Products')
	DROP VIEW POSplus_TagLoc_Products
GO

IF NOT EXISTS 
(
	SELECT 
		* 
	FROM 
		INFORMATION_SCHEMA.COLUMNS 
	WHERE COLUMN_NAME='LabelWidth' AND 
		TABLE_NAME='Products'
)
	ALTER TABLE Products ADD LabelWidth varchar(8)
GO

CREATE VIEW POSplus_TagLoc_Products
AS
SELECT     dbo.TagSubscription.LocAisle, dbo.TagSubscription.LocBlock, dbo.TagSubscription.LocSeq, dbo.Products.upc, dbo.Products.cert_code, 
                      dbo.Products.description, dbo.Products.cost, dbo.Products.normal_price, dbo.Products.pricemethod, dbo.Products.groupprice, dbo.Products.quantity, 
                      dbo.Products.special_price, dbo.Products.specialcost, dbo.Products.specialpricemethod, dbo.Products.specialgroupprice, 
                      dbo.Products.specialquantity, dbo.Products.start_date, dbo.Products.end_date, dbo.Products.target_margin, dbo.Products.department, 
                      dbo.Products.section, dbo.Products.vendor, dbo.Products.pack, dbo.Products.size, dbo.Products.tax, dbo.Products.foodstamp, dbo.Products.scale, 
                      dbo.Products.dsd, dbo.Products.upc_link, dbo.Products.mixmatchcode, dbo.Products.modified, dbo.Products.cost_modified, dbo.Products.active, 
                      dbo.Products.advertised, dbo.Products.whomodified, dbo.Products.picture_name, dbo.Products.longdescription, dbo.Products.seconddescription, 
                      dbo.Products.tareweight, dbo.Products.discount, dbo.Products.unitofmeasure, dbo.Products.effectiveschedule, dbo.Products.wicable, 
                      dbo.Products.validage, dbo.Products.deleted, dbo.Products.currentbatchno, dbo.Products.maxdiscount, dbo.Products.attributes, 
                      dbo.Products.QualifiedPercent, dbo.Products.QualifiedFlatAmount, dbo.Products.TOB, dbo.Products.Created, dbo.Products.CreatedBy, 
                      dbo.Products.Points, dbo.Products.groupprice2, dbo.Products.quantity2, dbo.Products.groupprice3, dbo.Products.quantity3, dbo.Products.groupprice4, 
                      dbo.Products.quantity4, dbo.Products.groupprice5, dbo.Products.quantity5, dbo.Products.Specialgroupprice2, dbo.Products.Specialquantity2, 
                      dbo.Products.Specialgroupprice3, dbo.Products.Specialquantity3, dbo.Products.Specialgroupprice4, dbo.Products.Specialquantity4, 
                      dbo.Products.Specialgroupprice5, dbo.Products.Specialquantity5, dbo.Products.StartTime, dbo.Products.EndTime, dbo.Products.LookupRanking, 
                      dbo.Products.FSACategory, dbo.Products.CGID, dbo.Products.CGPurchaseRequired, dbo.Products.POSplus_InvSeenInStoreFlag, 
                      dbo.Products.RecentMoveScratch, dbo.Products.DealEndDate, dbo.Products.Movement_13week, dbo.Products.SVTregPrintTagFlag, 
                      dbo.Products.SVTprintTagFlag, dbo.Products.HowManyShelfTags, dbo.Products.ShelfTagType, dbo.Products.PurchaseLimit, dbo.Products.WhoCreated, 
                      dbo.Products.Hosted, dbo.Products.EDLPcreated, dbo.Products.EDLPprinted, dbo.Products.UOM_Price, dbo.Products.UOM_Desc, dbo.Products.UOM, 
                      dbo.Products.Unit_NP, dbo.Products.Unit_SP, dbo.Products.UnitsPer, dbo.Products.EDLPflag, dbo.Products.LabelWidth, 
                      dbo.Products.Movement_13week / 91.0 AS AvgDailyMovement
FROM         dbo.Products LEFT OUTER JOIN
                      dbo.TagSubscription ON dbo.Products.upc = dbo.TagSubscription.UPC
GO

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='POSplus_catalina_signs')
BEGIN
	CREATE TABLE POSplus_catalina_signs
	(
		clu varchar(10),
		start_date datetime,
		end_date datetime,
		expire datetime,
		qualifier varchar(60),
		reward varchar(45),
		upc varchar(13),
		item_name varchar(30)
	)
END
GO


IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='POSplus_PrintManagerDisplays')
BEGIN
	CREATE TABLE POSplus_PrintManagerDisplays
	(
		DisplayId int IDENTITY(1,1) NOT NULL,
		Upc varchar(13) NOT NULL,
		BatchNo varchar(25) NULL,
		JobName varchar(60) NOT NULL,
		LocAisle int NOT NULL,
		LocBlock int NOT NULL,
		LocSeq int NOT NULL,
		Type varchar(8) NOT NULL,
		Copies int NOT NULL DEFAULT((1)),
		CONSTRAINT PK_POSplus_PrintManagerDisplays PRIMARY KEY CLUSTERED
		(
			DisplayId
		),
		CONSTRAINT IX_POSplus_PrintManagerDislpays UNIQUE NONCLUSTERED
		(
			Upc ASC,
			JobName ASC,
			LocAisle ASC,
			LocBlock ASC,
			LocSeq ASC
		)
	)
END
GO

IF EXISTS (
		SELECT *
		FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
		WHERE CONSTRAINT_NAME = 'unique_print_job'
		)
	ALTER TABLE POSplus_PrintManagerDisplays
	DROP CONSTRAINT unique_print_job
GO

IF OBJECT_ID(N'dbo.check_job_exists', N'FN') IS NOT NULL
	DROP FUNCTION check_job_exists
GO

CREATE FUNCTION check_job_exists
(
	@jobName VARCHAR(50),
	@type VARCHAR(10)
)
RETURNS INT
AS
BEGIN
	DECLARE @result INT

	IF EXISTS (
			SELECT *
			FROM POSplus_PrintManagerDisplays
			WHERE JobName = @jobName AND Type != @type
			)
		SET @result = 1
	ELSE
		SET @result = 0
	
	RETURN @result
END
GO

IF NOT EXISTS(
		SELECT *
		FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
		WHERE CONSTRAINT_NAME = 'unique_print_job'
		)
BEGIN
	ALTER TABLE POSplus_PrintManagerDisplays
	ADD CONSTRAINT unique_print_job CHECK (dbo.check_job_exists(JobName, Type) <> 1)
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name='POSplus_SignsPrintInfo')
BEGIN
	IF NOT EXISTS 
	(
		SELECT 
			* 
		FROM 
			INFORMATION_SCHEMA.COLUMNS 
		WHERE COLUMN_NAME='Uom' AND 
			TABLE_NAME='POSplus_SignsPrintInfo'
	)
		ALTER TABLE POSplus_SignsPrintInfo ADD Uom varchar(10)
		
	IF NOT EXISTS
	(
		SELECT *
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE COLUMN_NAME='StartDate' AND
			TABLE_NAME='POSplus_SignsPrintInfo'
	)
		ALTER TABLE POSplus_SignsPrintInfo ADD StartDate datetime
END
GO

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='POSplus_SignsPrintInfo')
BEGIN
	CREATE TABLE POSplus_SignsPrintInfo
	(
		Upc varchar(13) NOT NULL,
		Description1 varchar(26) NULL,
		Description2 varchar(30) NULL,
		Description3 varchar(30) NULL,
		SpecialPrice varchar(12) NULL,
		NormalPrice money NULL,
		UnitSP float NULL,
		Size varchar(9) NULL,
		WeekEnding datetime NULL,
		YouSave money NULL,
		Description4 varchar(39) NULL,
		Description5 varchar(39) NULL,
		BatchNo varchar(30) NOT NULL DEFAULT (''),
		Uom varchar(10) NULL,
		StartDate datetime NULL
	)
END
GO

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='POSplus_EZBT_History')
BEGIN
	CREATE TABLE POSplus_EZBT_History
	(
		HistoryId int IDENTITY(1,1) NOT NULL,
		Date datetime NOT NULL,
		TearListPrinted bit NOT NULL CONSTRAINT DF_POSplus_EZBT_History_TearListPrinted DEFAULT((0)),
		CONSTRAINT PK_POSplus_EZBT_History_1 PRIMARY KEY CLUSTERED
		(
			HistoryId ASC
		)
		ON
			[PRIMARY]
	)
	ON
		[PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='POSplus_EZBT_HistoryData')
BEGIN
	CREATE TABLE POSplus_EZBT_HistoryData
	(
		HistoryDataId int IDENTITY(1,1) NOT NULL,
		HistoryFK int NOT NULL,
		Upc varchar(13) NOT NULL,
		Description varchar(30) NULL,
		Vendor int NULL,
		Cert_Code varchar(15) NULL,
		AvgDailyMovement float NULL,
		Pack smallint NULL,
		Size varchar(9) NULL,
		Unit_NP float NULL,
		Uom varchar(9) NULL,
		Normal_Price money NULL,
		PriceMethod smallint NOT NULL,
		GroupPrice money NULL,
		Quantity smallint NULL,
		LocAisle int NULL,
		LocBlock int NULL,
		LocSeq int NULL,
		CONSTRAINT PK_POSplus_EZBT_HistoryData PRIMARY KEY CLUSTERED
		(
			HistoryDataId ASC
		)
		ON
			[PRIMARY]
	)
	ON
		[PRIMARY]
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name='POSplus_PrintJobs')
	DROP VIEW POSplus_PrintJobs
GO

CREATE VIEW POSplus_PrintJobs
AS
	SELECT
		JobName,
		SUM(Copies) AS Items,
		Type,
		BatchNo
	FROM
		POSplus_PrintManagerDisplays
	GROUP BY
		JobName,
		Type,
		BatchNo
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name='POSplus_SpecialPrintInfo')
	DROP VIEW POSplus_SpecialPrintInfo
GO

CREATE VIEW POSplus_SpecialPrintInfo
AS
	SELECT DISTINCT
		r.upc,					r.description,
		r.cert_code,			r.pricemethod,
		r.groupprice,			r.normal_price,
		r.quantity,				r.pack,
		r.size,					r.vendor,
		r.Hosted,				r.LocAisle,
		r.LocBlock,				r.LocSeq,
		r.Unit_NP,				r.uom,
		r.AvgDailyMovement,		r.SVTregPrintTagFlag,
		s.BatchNo,				s.specialpricemethod,
		s.special_price,		s.specialgroupprice,
		s.specialquantity,		s.Unit_SP,
		s.Rehang,				s.WhereToBuy,
		s.start_date,			s.end_date,s.DealendDate,
		s.PalletCode,			POSplus_PrintManagerDisplays.JobName
	FROM
	(
		SELECT
			upc,					description,
			cert_code,				pricemethod,
			groupprice,				normal_price,
			quantity,				pack,
			size,					vendor,
			Hosted,					LocAisle,
			LocBlock,				LocSeq,
			Unit_NP,				uom,
			AvgDailyMovement,		SVTregPrintTagFlag
		FROM
			POSplus_TagLoc_Products
	) r
	INNER JOIN
	(
		SELECT	
			upc,					LocAisle,
			LocBlock,				LocSeq,
			BatchNo,				specialpricemethod,
			special_price,			specialgroupprice,
			specialquantity,		Unit_SP,
			Rehang,					WhereToBuy,
			start_date,				end_date,
			DealEndDate,			PalletCode
		FROM
			POSplus_TagLoc_ProdBats
	) s
	ON
		r.upc = s.upc AND
		ISNULL(r.LocAisle, 0) = ISNULL(s.LocAisle, 0) AND
		ISNULL(r.LocBlock, 0) = ISNULL(s.LocBlock, 0) AND
		ISNULL(r.LocSeq, 0) = ISNULL(s.LocSeq, 0)
	INNER JOIN
		POSplus_PrintManagerDisplays
	ON
		r.upc = POSplus_PrintManagerDisplays.upc AND
		s.BatchNo = POSplus_PrintManagerDisplays.BatchNo
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name='POSplus_SignsWithJobName')
	DROP VIEW POSplus_SignsWithJobName
GO

-- new
IF EXISTS (SELECT * FROM sysobjects WHERE name='POSplus_FutureSpecialPrintInfo')
	DROP VIEW POSplus_FutureSpecialPrintInfo
GO

CREATE VIEW POSplus_FutureSpecialPrintInfo
AS
	SELECT DISTINCT
		r.upc,					r.description,
		r.cert_code,			r.pricemethod,
		r.groupprice,			r.normal_price,
		r.quantity,				r.pack,
		r.size,					r.vendor,
		r.Hosted,				r.LocAisle,
		r.LocBlock,				r.LocSeq,
		r.Unit_NP,				r.uom,
		r.AvgDailyMovement,		r.SVTregPrintTagFlag,
		s.BatchNo,				s.specialpricemethod,
		s.special_price,		s.specialgroupprice,
		s.specialquantity,		s.Unit_SP,
		s.Rehang,				s.WhereToBuy,
		s.start_date,			s.end_date,s.DealendDate,
		s.PalletCode,			POSplus_PrintManagerDisplays.JobName
	FROM
	(
		SELECT
			upc,					description,
			cert_code,				pricemethod,
			groupprice,				normal_price,
			quantity,				pack,
			size,					vendor,
			Hosted,					LocAisle,
			LocBlock,				LocSeq,
			Unit_NP,				uom,
			AvgDailyMovement,		SVTregPrintTagFlag
		FROM
			POSplus_TagLoc_Products
	) r
	INNER JOIN
	(
		SELECT	
			upc,					LocAisle,
			LocBlock,				LocSeq,
			BatchNo,				specialpricemethod,
			special_price,			specialgroupprice,
			specialquantity,		Unit_SP,
			Rehang,					WhereToBuy,
			start_date,				end_date,
			DealEndDate,			PalletCode
		FROM
			POSplus_TagLoc_DSD_FPB
	) s
	ON
		r.upc = s.upc AND
		ISNULL(r.LocAisle, 0) = ISNULL(s.LocAisle, 0) AND
		ISNULL(r.LocBlock, 0) = ISNULL(s.LocBlock, 0) AND
		ISNULL(r.LocSeq, 0) = ISNULL(s.LocSeq, 0)
	INNER JOIN
		POSplus_PrintManagerDisplays
	ON
		r.upc = POSplus_PrintManagerDisplays.upc AND
		s.BatchNo = POSplus_PrintManagerDisplays.BatchNo
GO

-- end
CREATE VIEW POSplus_SignsWithJobName
AS
	SELECT
		s.upc,			Description1,
		Description2,	Description3,
		SpecialPrice,	NormalPrice,
		UnitSP,			Size,
		WeekEnding,		YouSave,
		Description4,	Description5,
		JobName,		Type,
		Copies, 		s.BatchNo,
		s.Uom,			s.StartDate
	FROM
		POSplus_SignsPrintInfo s
	INNER JOIN
		POSplus_PrintManagerDisplays d
	ON
		s.upc = d.upc and s.BatchNo = d.BatchNo
GO

IF EXISTS (SELECT * FROM sysobjects	WHERE name='POSplus_RegularPrintInfo')
	DROP VIEW POSplus_RegularPrintInfo
GO

CREATE VIEW POSplus_RegularPrintInfo
AS
	SELECT
		p.LocAisle,			p.LocBlock,
		p.LocSeq,			p.upc,
		p.cert_code,		p.description,
		p.normal_price,		p.pricemethod,
		p.groupprice,		p.quantity,
		p.vendor,			p.pack,
		p.size,				p.Unit_NP,
		p.uom,				p.AvgDailyMovement,
		p.Hosted,			d.JobName
	FROM
		POSplus_PrintManagerDisplays d
	INNER JOIN
		POSplus_TagLoc_Products p
	ON
		d.upc = p.upc AND
		ISNULL(d.LocAisle, 0) = ISNULL(p.LocAisle, 0) AND
		ISNULL(d.LocBlock, 0) = ISNULL(p.LocBlock, 0) AND
		ISNULL(d.LocSeq, 0) = ISNULL(p.LocSeq, 0)
	WHERE
		d.Type = '18UPREG' OR
		d.Type = '18UPREGD' OR
		d.Type = '6UPSB' OR
		d.Type = '32UPREG' OR
		d.Type = '32UPREGD' OR
		d.Type = '12UPSB'
GO

-- IN HERE
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'POSplus_RegularBatchesPrintInfo')
	DROP VIEW POSplus_RegularBatchesPrintInfo
GO

CREATE VIEW POSplus_RegularBatchesPrintInfo
AS
	SELECT     
		d.JobName,		p.LocAisle, 
		p.LocBlock, 	p.LocSeq, 
		p.upc,			p.BatchNo, 
		p.cert_code,    p.description, 
		p.normal_price, p.pricemethod, 
		p.groupprice, 	p.quantity, 
		p.Vendor,	    p.pack, 
		p.size,			p.Hosted, 
		p.UOM,			p.Unit_NP, 
		p.AvgDailyMovement
	FROM         
		POSplus_PrintManagerDisplays AS d 
	INNER JOIN
		POSplus_TagLoc_ProdBats AS p
	ON 
		d.Upc = p.upc AND 
		ISNULL(d.LocAisle, 0) = ISNULL(p.LocAisle, 0) AND 
		ISNULL(d.LocBlock, 0) = ISNULL(p.LocBlock, 0) AND 
		ISNULL(d.LocSeq, 0) = ISNULL(p.LocSeq, 0) AND
		d.BatchNo = p.BatchNo
	WHERE     
		(d.Type = '18UPREG') OR
		(d.Type = '18UPREGD') OR
		(d.Type = '6UPSB') OR
		(d.Type = '32UPREG') OR
		(d.Type = '32UPREGD') OR
		(d.Type = '12UPSB')
GO
-- END HERE





IF EXISTS (SELECT * FROM sysobjects WHERE type='P' AND name='sp_AllEdlps')
	DROP PROCEDURE sp_AllEdlps
GO

CREATE PROCEDURE sp_AllEdlps
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT
		*
	FROM
		POSplus_TagLoc_Products
	WHERE
		EDLPflag = 1
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type='P' AND name='sp_ClearEdlpTearList')
	DROP PROCEDURE sp_ClearEdlpTearList
GO

CREATE PROCEDURE sp_ClearEdlpTearList
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE
		POSplus_EZBT_History
	SET
		TearListPrinted = 1
	WHERE
		TearListPrinted = 0
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type ='P' AND name='sp_NonPrintedEdlps')
	DROP PROCEDURE sp_NonPrintedEdlps
GO	

CREATE PROCEDURE sp_NonPrintedEdlps
AS
BEGIN
	SET NOCOUNT ON;
	SELECT
		*
	FROM
		POSplus_TagLoc_Products
	WHERE
		EDLPflag = 1 AND (EDLPprinted IS NULL OR EDLPprinted < EDLPCreated)
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_TearList')
	DROP PROCEDURE sp_TearList
GO

CREATE PROCEDURE sp_TearList
AS
BEGIN
	SET NOCOUNT ON;
	SELECT
		data.Upc,
		data.Description,
		data.LocAisle,
		data.LocBlock,
		data.LocSeq
	FROM
	(
		SELECT
			*
		FROM
			POSplus_EZBT_History
		WHERE
			TearListPrinted = 0
	) history
	INNER JOIN
		POSplus_EZBT_HistoryData data
	ON
		History.HistoryID = data.HistoryFK
	LEFT OUTER JOIN
	(
		SELECT
			Upc, 
			EDLPflag
		FROM
			POSplus_TagLoc_Products
		WHERE
			EDLPflag = 1
	) products
	ON
		data.Upc = products.Upc
	WHERE
		products.Upc IS NULL
	ORDER BY
		ISNULL(data.LocAisle, 999999),
		data.LocBlock,
		data.LocSeq
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_BackUpCurrentEdlp')
	DROP PROCEDURE sp_BackUpCurrentEdlp
GO

CREATE PROCEDURE sp_BackUpCurrentEdlp
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO POSplus_EZBT_History (Date)
	VALUES (GETDATE())

	INSERT INTO POSplus_EZBT_HistoryData
	(
		HistoryFK,			Upc,
		Description,		Vendor,
		Cert_Code,			AvgDailyMovement,
		Pack,				Size,
		Unit_NP,			Uom,
		Normal_Price,		PriceMethod,
		GroupPrice,			Quantity,
		LocAisle,			LocBlock,
		LocSeq
	)
	SELECT
		IDENT_CURRENT('POSplus_EZBT_History'),
		Upc,				Description,
		Vendor,				Cert_Code,
		AvgDailyMovement,	Pack,
		Size,				Unit_NP,
		Uom,				Normal_Price,
		PriceMethod,		GroupPrice,
		Quantity,			LocAisle,
		LocBlock,			LocSeq
	FROM
		POSplus_TagLoc_Products
	WHERE
		EDLPflag = 1
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_SetEdlpPrintedDate')
	DROP PROCEDURE sp_SetEdlpPrintedDate
GO

CREATE PROCEDURE sp_SetEdlpPrintedDate
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE
		Products
	SET
		EDLPprinted = GETDATE()
	WHERE
		EDLPflag = 1
	EXECUTE sp_BackUpCurrentEdlp
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_GetSmallLabelBatches')
	DROP PROCEDURE sp_GetSmallLabelBatches
GO

CREATE PROCEDURE sp_GetSmallLabelBatches 
AS 
BEGIN 
	SET NOCOUNT ON; 

	SELECT 
		b.BatchNo, 
		a.Description, 
		a.Created, 
		a.AppliedWhen, 
		COUNT(b.BatchNo) AS Items
	FROM 
	( 
		SELECT 
			BatchNo, 
			Description, 
			Created, 
			AppliedWhen 
		FROM 
			ProductBatchesHeader 
		WHERE 
			BatchNo LIKE 'small labels%' OR 
			BatchNo LIKE 'smalllabels%'
	) a
	INNER JOIN 
		ProductBatches b 
	ON 
		a.BatchNo = b.BatchNo 
	GROUP BY 
		b.BatchNo, 
		a.Description, 
		a.Created, 
		a.AppliedWhen 
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_SetSmallLabel')
	DROP PROCEDURE sp_SetSmallLabel
GO

CREATE PROCEDURE sp_SetSmallLabel 
	@BatchNo varchar(25) 
AS
BEGIN 
	SET NOCOUNT ON; 

	UPDATE 
		Products
	SET 
		Products.LabelWidth = 'SM'
	FROM 
		Products 
	INNER JOIN 
	( 
		SELECT 
			Upc 
		FROM 
			ProductBatches 
		WHERE	
			BatchNo = @BatchNo
	) b 
	ON 
		Products.Upc = b.Upc  
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_UnsetSmallLabel')
	DROP PROCEDURE sp_UnsetSmallLabel
GO

CREATE PROCEDURE sp_UnsetSmallLabel 
	@BatchNo varchar(25) 
AS 
BEGIN 

	SET NOCOUNT ON; 

	UPDATE 
		Products 
	SET 
		LabelWidth = NULL 
	FROM 
		Products 
	INNER JOIN 
	( 
		SELECT 
			Upc 
		FROM 
			ProductBatches 
		WHERE 
			BatchNo = @BatchNo
	) b 
	ON 
		Products.Upc = b.Upc 
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_ChangeSmallLabel')
	DROP PROCEDURE sp_ChangeSmallLabel
GO

CREATE PROCEDURE sp_ChangeSmallLabel
	@Upc varchar(13),
	@Flag varchar(3) = NULL	
AS
BEGIN
	SET NOCOUNT ON;

	IF @Flag IS NULL
	BEGIN
		UPDATE 
			Products
		SET 
			LabelWidth = NULL
		WHERE 
			Upc = @Upc
	END
	ELSE
	BEGIN
		UPDATE 
			Products
		SET 
			LabelWidth = @Flag
		WHERE 
			Upc = @Upc
	END
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_GetEdlpStatus')
	DROP PROCEDURE sp_GetEdlpStatus
GO

CREATE PROCEDURE sp_GetEdlpStatus
	@Upc varchar(13)
AS
BEGIN

	SET NOCOUNT ON;

	IF EXISTS (SELECT * FROM Products WHERE Upc = @Upc AND EDLPflag = 1)
		RETURN 1
	ELSE
		RETURN 0

END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_UpcExists')
	DROP PROCEDURE sp_UpcExists
GO

CREATE PROCEDURE sp_UpcExists
	@Upc varchar(13)
AS
BEGIN

	SET NOCOUNT ON;

	IF EXISTS (SELECT * FROM Products WHERE Upc = @Upc)
		RETURN 1
	ELSE
		RETURN 0
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_GetCurrentSmallValue')
	DROP PROCEDURE sp_GetCurrentSmallValue
GO

CREATE PROCEDURE sp_GetCurrentSmallValue
	@Upc varchar(13)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT
		Upc,
		LabelWidth
	FROM
		Products
	WHERE
		Upc = @Upc
END
GO